﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MINERP.DAL.GoodsDelivery
{
    public class DeliverFindDAL
    {
        string conStr = ConfigurationManager.ConnectionStrings["sqlCon"].ToString();

        #region 获取所有的商户列表
        /// <summary>
        /// 获取所有的商户列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetShopDT()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select ShopId,ShopName from shop";            //商户有删除的吗?
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 从shop_bill中获取还未分配到发货格子的部分
        /// <summary>
        /// 从shop_bill中获取还未分配到发货格子的部分
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public DataTable GetNoDistributeBill(int shopId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                SqlCommand cmd = new SqlCommand("Proc_GetNoDeliveryBillByShopId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("shopId", shopId));
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 从shop_bill中获取还未分配到发货格子的部分(按除shopid以外的其他条件)
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="ctmName"></param>
        /// <param name="orderProductCode"></param>
        /// <returns></returns>
        public DataTable GetNoDistributeBill(string ctmName, string orderProductCode)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                SqlCommand cmd = new SqlCommand("Proc_GetNoDeliveryBillByOtherReasons", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("ctmName", ctmName));
                cmd.Parameters.Add(new SqlParameter("orderProductCode", orderProductCode));
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 从DeliveryInfo获取已分配到发货格子的部分
        /// <summary>
        /// 从DeliveryInfo获取已分配到发货格子的部分
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="noPrintStatus"></param>
        /// <returns></returns>
        public DataTable GetNoPrintBill(int shopId, int noPrintStatus)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,BillOrderCode,Shop.ShopId,Shop.ShopName from DeliveryInfo,Shop_bill,Shop";
                sql += " where Shop.ShopId=" + shopId;
                sql += " and DeliveryInfo.BillId=Shop_bill.BillId and shop_bill.shopId=shop.shopid and DeliveryInfo.status=" + noPrintStatus;
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 从DeliveryInfo获取已分配到发货格子的部分(按出shopid以外的条件查询)
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="ctmName"></param>
        /// <param name="orderProductCode"></param>
        /// <param name="noPrintStatus"></param>
        /// <returns></returns>
        public DataTable GetNoPrintBill(string ctmName, string orderProductCode, int noPrintStatus)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,max(BillOrderCode) as BillOrderCode,max(Shop.ShopId) as ShopId,max(Shop.ShopName) as ShopName";
                sql += " from DeliveryInfo inner join shop_bill on deliveryinfo.billid=shop_bill.billId and deliveryInfo.status=" + noPrintStatus;
                sql += " inner join shop on shop_bill.shopid=shop.shopid";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billid";
                if (ctmName.Length > 0)
                    sql += " and bill_detail.ctmname like '%" + ctmName + "%'";
                if (orderProductCode.Length > 0)
                    sql += " and bill_detail.orderinfocode like '%" + orderProductCode + "%'";
                sql += " group by deliveryinfo.billid";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取已打印，但还未发货的账单
        /// <summary>
        /// 获取已打印，但还未发货的账单
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="printedStatus"></param>
        /// <returns></returns>
        public DataTable GetPrintedBill(int shopId, int printedStatus)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,BillOrderCode,Shop.ShopId,Shop.ShopName,DeliverTime,ArrivalTime,TuoYunDanCode,Num,StationRemark,ReceiveStationRemark,DeliverObject,ShopMoney,SelfMoney,OtherMoney,DeliveryInfo.DeliverId,DeliveryInfo.Remark from DeliveryInfo,Shop_bill,Shop";
                sql += " where Shop.ShopId=" + shopId;
                sql += " and DeliveryInfo.BillId=Shop_bill.BillId and shop_bill.shopId=shop.shopid and DeliveryInfo.status=" + printedStatus;
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取已打印，但还未发货的账单(按出shopid以外的条件查询)
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="printedStatus"></param>
        /// <returns></returns>
        public DataTable GetPrintedBill(string ctmName, string orderProductCode, int printedStatus)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,max(BillOrderCode) as BillOrderCode,max(Shop.ShopId) as ShopId,max(Shop.ShopName) as ShopName,max(DeliverTime) as DeliverTime,max(ArrivalTime) as ArrivalTime,max(TuoYunDanCode) as TuoYunDanCode,max(Num) as Num,max(StationRemark) as StationRemark,max(ReceiveStationRemark) as ReceiveStationRemark,max(DeliverObject) as DeliverObject,max(ShopMoney) as ShopMoney,max(SelfMoney) as SelfMoney,max(OtherMoney) as OtherMoney,max(DeliveryInfo.DeliverId) as DeliverId,max(DeliveryInfo.Remark) as Remark,username";
                sql += " from DeliveryInfo inner join Shop_bill on deliveryinfo.billid=shop_bill.billid and deliveryinfo.status=" + printedStatus;
                sql += " inner join shop on shop_bill.shopid=shop.shopid";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billid";
                sql += " inner join users on users.userid=DeliveryInfo.userid";
                if (ctmName.Length > 0)
                    sql += " and bill_detail.ctmname like '%" + ctmName + "%'";
                if (orderProductCode.Length > 0)
                    sql += " and bill_detail.orderinfocode like '%" + orderProductCode + "%'";
                sql += " group by deliveryinfo.billid,username";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 获取在开始时间之后的指定商户的已发货的账单
        /// <summary>
        /// 获取在开始时间之后的指定商户的已发货的账单
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="deliverredStatus"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        public DataTable GetDeliverredBill(int shopId, int deliverredStatus, DateTime startTime, DateTime endTime)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,BillOrderCode,Shop.ShopId,Shop.ShopName,DeliverTime,ArrivalTime,TuoYunDanCode,Num,StationRemark,ReceiveStationRemark,DeliverObject,ShopMoney,SelfMoney,OtherMoney,DeliveryInfo.DeliverId,DeliveryInfo.Remark ,users.username as username from DeliveryInfo,Shop_bill,Shop,users";
                sql += " where Shop.ShopId=" + shopId + " and deliverTime>='" + startTime + "' and deliverTime<='" + endTime + "' and users.userid=DeliveryInfo.userid";
                sql += " and DeliveryInfo.BillId=Shop_bill.BillId and shop_bill.shopId=shop.shopid and DeliveryInfo.status=" + deliverredStatus;
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取在开始时间之后的指定商户的已发货的账单(按除shopid以外的条件查询)
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="deliverredStatus"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        public DataTable GetDeliverredBill(string ctmName, string orderProductCode, int deliverredStatus, DateTime startTime, DateTime endTime)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select DeliveryInfo.BillId,max(BillOrderCode) as BillOrderCode,max(Shop.ShopId) as ShopId,max(Shop.ShopName) as ShopName,max(DeliverTime) as DeliverTime,max(ArrivalTime) as ArrivalTime,max(TuoYunDanCode) as TuoYunDanCode,max(Num) as Num,max(StationRemark) as StationRemark,max(ReceiveStationRemark) as ReceiveStationRemark,max(DeliverObject) as DeliverObject,max(ShopMoney) as ShopMoney,max(SelfMoney) as SelfMoney,max(OtherMoney) as OtherMoney,max(DeliveryInfo.DeliverId) as DeliverId,max(DeliveryInfo.Remark) as Remark,users.username as username";
                sql += " from DeliveryInfo inner join shop_bill on deliveryinfo.billid=shop_bill.billid and deliveryinfo.status=" + deliverredStatus + " and deliverTime>='" + startTime + "' and deliverTime<='" + endTime + "'";
                sql += " inner join Shop on shop_bill.shopid=shop.shopid";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billid";
                sql += " inner join users  on users.userid=DeliveryInfo.userid";
                if (ctmName.Length > 0)
                    sql += " and bill_detail.ctmname like '%" + ctmName + "%'";
                if (orderProductCode.Length > 0)
                    sql += " and bill_detail.orderinfocode like '%" + orderProductCode + "%'";
                sql += " group by deliveryinfo.billid,username";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 账单内的产品
        /// <summary>
        /// 账单内的产品
        /// </summary>
        /// <param name="billId"></param>
        /// <returns></returns>
        public DataTable GetOrderInfoByBillId(int billId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "SELECT CtmName,OrderInfoCode,OrderProductName FROM Bill_Detail ";
                sql += " WHERE Bill_Detail.BillId = " + billId;
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 按客户名、产品编号筛选
        /// <summary>
        /// 按客户名、产品编号筛选
        /// </summary>
        /// <param name="billIds"></param>
        /// <param name="ctmName"></param>
        /// <param name="orderProductCode"></param>
        /// <returns></returns>
        public List<int> FilterBill(List<int> billIds, string ctmName, string orderProductCode)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                List<int> filterBillIds = new List<int>();
                string sql = "select orderinfo_bk.CtmName,orderinfo_bk.OrderProductCode from bill_detail,orderinfo_bk where orderinfo_bk.parentproductid=0 and orderinfo_bk.orderproductcode=bill_detail.orderinfocode and billid=";
                SqlCommand cmd = new SqlCommand();

                //筛选条件
                string filterStr = "";
                if (ctmName.Length > 0)
                    filterStr = "and CtmName like '%" + ctmName + "%' ";
                if (orderProductCode.Length > 0)
                    filterStr += "and OrderProductCode='" + orderProductCode + "'";
                filterStr = filterStr.Substring(4);

                cmd.Connection = con;
                string sql2 = "";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                foreach (int billId in billIds)
                {
                    dt.Rows.Clear();
                    sql2 = sql + billId;
                    cmd.CommandText = sql2;
                    adapter.Fill(dt);
                    DataRow[] rs = dt.Select(filterStr);
                    if (rs != null && rs.Length > 0)
                        filterBillIds.Add(billId);
                }
                return filterBillIds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改商户邮箱
        /// <summary>
        /// 修改商户邮箱
        /// </summary>
        /// <param name="shopId"></param>
        /// <param name="mailAddr"></param>
        public void UpdateShopMail(int shopId, string mailAddr)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "update shop set EmailAddr='" + mailAddr + "' where shopId=" + shopId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 商户的email地址
        /// <summary>
        /// 商户的email地址
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public string GetShopMailAddr(int shopId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select EmailAddr from shop where shopId=" + shopId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                string r = "";
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    r = obj.ToString();
                return r;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
    }
}
